package com.css.fxfzypg.manage.sampling.repository.impl;

import com.css.fxfzypg.manage.sampling.repository.SamplingReqository;
import com.css.fxfzypg.manage.sampling.repository.entity.SamplingEntity;
import com.css.fxfzypg.manage.sampling.vo.SamplingParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class SamplingReqositoryImpl implements SamplingReqository {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional
    public boolean insertSurvey(SamplingEntity samplingEntity) {
        String sql = "insert into ypg_sampling_survey values(:id,:taskName,:taskCode,:fieldSurveyName,:province,:provinceId,:city,:cityId,:area,:areaId,:township,:townshipId,:longitude,:latitude,:totalHouseholdNum,:totalPopulation,:actualResidentPopulation,:migrantWorkers,:childrenProportion,:youngProportion,:oldAgeProportion,:perCapitaIncome,:mainEconomicSources,:trafficConditions,:environment,:houseNum,:roomAverageNum,:mainBuildingMaterials,:highStructure,:steelStructure,:concreteStructure,:masonryStructure,:brickWoodStructure,:soilStructure,:woodStructure,:stoneStructure,:otherStructure,:build70sBefore,:build70s,:build80s,:build90s,:build00s,:build10s,:build20s,:isSchool,:studentNum,:classroomNum,:isHospital,:doctorNum,:bedNum,:isDisaster,:disasterType,:disasterScale,:isReservoir,:damType,:reservoirCapacity,:remarks,:isSubmit,:delFlag,:createName,:createId,:createTime,:submitter,:submitterId,:submitTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samplingEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }


    @Override
    public Map<String,Object> querySubmitOrNot(String submitFlag,String taskCode, int curPage, int pageSize, String userId) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_sampling_survey where del_flag = '0' and create_id = '"+userId+"'and task_code = '"+ taskCode + "' and is_submit = '"+ submitFlag+"\'");
            if(submitFlag.equals("0")){
                sql.append(" order by create_time desc");
            }else{
                sql.append(" order by submit_time desc");
            }
            int firstIndex=(curPage-1) * pageSize;
            int lastIndex=curPage * pageSize;
            StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingEntity.class)).size();
            List<SamplingEntity> samplingEntityList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamplingEntity.class));
            Map<String,Object> map=new HashMap();
            map.put("total",total);
            map.put("rows",samplingEntityList);
            return map;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public boolean updateIsSubmit(Map<String,String> map) {
        String sql = " update ypg_sampling_survey set is_submit = '1', submitter = ?, submitter_id = ?, submit_time = ? where id = ?";
        return jdbcTemplate.update(sql,map.get("userName"),map.get("userId"),new Date(),map.get("id")) > 0;
    }

    @Override
    public SamplingEntity queryData(String id) {
        String sql = "select * from ypg_sampling_survey where del_flag = '0' and id = ?";
        try {
            SamplingEntity samplingEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SamplingEntity.class),id);
            return samplingEntity;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public boolean modifyData(SamplingEntity samplingEntity) {
        String sql = "update ypg_sampling_survey set total_household_num=:totalHouseholdNum,total_population=:totalPopulation,actual_resident_population=:actualResidentPopulation,migrant_workers=:migrantWorkers,children_proportion=:childrenProportion,young_proportion=:youngProportion,old_age_proportion=:oldAgeProportion," +
                "per_capita_income=:perCapitaIncome,main_economic_sources=:mainEconomicSources,traffic_conditions=:trafficConditions,environment=:environment,house_num=:houseNum,room_average_num=:roomAverageNum,main_building_materials=:mainBuildingMaterials,high_structure=:highStructure," +
                "steel_structure=:steelStructure,concrete_structure=:concreteStructure,masonry_structure=:masonryStructure,brick_wood_structure=:brickWoodStructure,soil_structure=:soilStructure,wood_structure=:woodStructure,stone_structure=:stoneStructure,other_structure=:otherStructure," +
                "build70s_before=:build70sBefore,build70s=:build70s,build80s=:build80s,build90s=:build90s,build00s=:build00s,build10s=:build10s,build20s=:build20s,is_school=:isSchool,student_num=:studentNum,classroom_num=:classroomNum,is_hospital=:isHospital,doctor_num=:doctorNum,bed_num=:bedNum," +
                "is_disaster=:isDisaster,disaster_type=:disasterType,disaster_scale=:disasterScale,is_reservoir=:isReservoir,dam_type=:damType,reservoir_capacity=:reservoirCapacity,remarks=:remarks,is_submit=:isSubmit,submitter=:submitter,submitter_id=:submitterId,submit_time=:submitTime where id = '"+ samplingEntity.getId()+"\'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samplingEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String, Object> querySamplingData(SamplingParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT t1.id,t1.task_code,t1.field_survey_name,t1.province,t1.city,t1.area,t1.township,t1.longitude,t1.latitude,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status " +
                "from ypg_sampling_survey t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())){
            whereSql.append(" and t1.province_id = '"+ queryParams.getProvinceId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCityId())){
            whereSql.append(" and t1.city_id = '"+ queryParams.getCityId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getAreaId())){
            whereSql.append(" and t1.area_id = '"+ queryParams.getAreaId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTownship())){
            whereSql.append(" and t1.township = '"+ queryParams.getTownship()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTownshipId())){
            whereSql.append(" and t1.township_id = '"+ queryParams.getTownshipId()+"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getFieldSurveyName())){
            whereSql.append(" and t1.field_survey_name like '%"+ queryParams.getFieldSurveyName() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getTaskName())){
            whereSql.append(" and t2.task_name like '%"+ queryParams.getTaskName() +"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())){
            whereSql.append(" and t1.submitter like '%"+ queryParams.getSubmitter()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())){
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '"+ queryParams.getStartTime() + "' and submit_time <= '"+ endTime +"\'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getIsSubmit())){
            whereSql.append(" and t1.is_submit = '"+ queryParams.getIsSubmit()+"\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total= jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamplingParams.class)).size();
        List<SamplingParams> samplingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamplingParams.class));
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",samplingData);
        return map;
    }

    @Override
    public boolean deleteSampling(String id) {
        String sql = "update ypg_sampling_survey set del_flag = 1  where id = ?";
        return jdbcTemplate.update(sql,id) > 0;
    }

}
